use master
go 

create database bbs
on
(
	name='bbs',
	filename='D:\bbs.mdf'
)
log on
(
	name='bbs_log',
	filename='D:\bbs_log.ldf'
)


use bbs
go

create table bbsUsers
(
	UID int identity(1,1),
	uName varchar(10) not null,
	uSex varchar(2) not null,
	uAge int not null,
	uPoint int not null
)


create table bbsSection
(
	sID int identity(1,1) ,
	sName varchar(10) not null ,
	sUid int 
)

create table bbsTopic
(
	tId int identity(1,1),
	tUID int,
	tSID int,
	tTitle varchar(100) not null,
	tMsg text not null,
	tTime datetime,
	tCount int
)


create table bbsReply
(
	rId int identity(1,1),
	rUId int,
	rTID int,
	rMsg text not null,
	rTime datetime
)


alter table bbsUsers add constraint PK_bbsUsers_UID primary key (UID)
alter table bbsUsers add constraint UQ_bbsUsers_uName unique (uName)
alter table bbsUsers add constraint CK_bbsUsers_uSex check(uSex='男' or uSex='女')
alter table bbsUsers add constraint CK_bbsUsers_uAge check(uAge>=15 and uAge<=60)
alter table bbsUsers add constraint CK_bbsUsers_uPoint check(uPoint>=0)

alter table bbsSection add constraint PK_bbsSection_sID primary key (sID)
alter table bbsSection add constraint FK_bbsSection_sUid foreign key(sUID) references bbsUsers(UID) 

alter table bbsTopic add constraint PK_bbsTopic_tID primary key (tID)
alter table bbsTopic add constraint FK_bbsTopic_tUID foreign key(tUID) references bbsUsers(UID)
alter table bbsTopic add constraint FK_bbsTopic_tSID foreign key(tSID) references bbsSection(sID)

alter table bbsReply add constraint PK_bbsReply_rID primary key (rID)
alter table bbsReply add constraint FK_bbsReply_rUID foreign key(rUID) references bbsUsers(UID)
alter table bbsReply add constraint Fk_bbsReply_rTID foreign key(rTID) references bbsTopic(tID)

--	1.现在有3个会员注册成功，请用一次插入多行数据的方法向bbsUsers表种插入3行记录，记录值如下：
--	  小雨点  女  20  0
--	  逍遥    男  18  4
--	  七年级生  男  19  2

insert into bbsUsers(uName,uSex,uAge,uPoint)
select'小雨点','女','20','0'union
select'逍遥','男','18','4'union
select'七年级生','男','19','2'

--	2.将bbsUsers表中的用户名和积分两列备份到新表bbsPoint表中，提示查询部分列:select 列名1，列名2 from 表名

select uName,uPoint into bbsPoint from bbsUsers



--	3.给论坛开设4个板块
--	  名称        版主名
--	  技术交流    小雨点
--	  读书世界    七年级生
--	  生活百科     小雨点
--	  八卦区       七年级生

insert into bbsSection(sName,sUid)
select'技术交流','3'union
select'读书世界','1'union
select'生活百科','3'union
select'八卦区','1'


--	4.向主贴和回帖表中添加几条记录
	  
--	   主贴：

--	  发帖人    板块名    帖子标题                帖子内容                发帖时间   回复数量
--	  逍遥      八卦区     范跑跑                 谁是范跑跑              2008-7-8   1
--	  七年级生  技术交流   .NET                   与JAVA的区别是什么呀？  2008-9-1   2
--	  小雨点   生活百科    今年夏天最流行什么     有谁知道今年夏天最流行  2008-9-10  0
--											      什么呀？
	   
insert into bbsTopic(tUID,tSID,tTitle,tMsg,tTime,tCount)
select '2','1','范跑跑','谁是范跑跑','2008-7-8','1'union
select '1','3','.NET','与Java的区别是什么呀？','2008-9-1','2'union
select '3','4','今年夏天最流行什么呀？','有谁知道今年夏天最流行什么呀？','2008-9-10','0'


--	   回帖：
--	   分别给上面三个主贴添加对应的回帖，回帖的内容，时间，回帖人自定

insert into bbsReply(rTID,rMsg,rTime,rUId)
select'1','不知道','2008-9-5','2'union
select'2','不认识','2008-9-10','1'union
select'3','不知道','2008-10-1','3'


  select * from bbsTopic
  select * from bbsReply
  select * from bbsUsers
  select * from bbsSection
--  在论坛数据库中完成以下题目
--1.查询出每个版块的版主编号，版主姓名和版块名称

select sUid 版主编号,uName 版主名称,sName 板块名称 from bbsUsers bu
	inner join bbsSection bs on bu.UID = bs.sUid

--2.查询出主贴的发帖时间在2008-9-15以后的主贴的发帖人编号，发帖人姓名，帖子的标题，帖子的内容和发帖时间

select tUID 发帖人编号,uName 发帖人姓名,tTitle 帖子标题,tMsg 帖子内容,tTime 发帖时间 from bbsUsers bu
	inner join bbsTopic bt on bu.UID = bt.tUID
		where tTime > '2008-09-15'

--3.查询出年龄在20以下的版主的编号，版主的名称和版块的名称

select sID 版主编号,uName 版主名称,sName 板块名称 from bbsUsers bu
	inner join bbsSection bs on bu.UID = bs.sUid
		where uAge < 20

--4.查询出回复数量最多的主贴的发帖人编号，发帖人姓名，主贴标题，主贴内容和回复数量

select tUID 发帖人编号,uName 发帖人姓名,tTitle 帖子标题,tMsg 帖子内容,tCount 回复数量 from bbsUsers bu
	inner join bbsTopic bt on bu.UID = bt.tUID
		where tCount = (select MAX(tCount) from bbsTopic)

--5.在主贴表中查询每个版块中每个用户的发帖总数

select sName 板块名称,uName 发帖人名称,COUNT(UID) 发帖总数 from bbsTopic bt
	RIGHT join bbsSection bs on bt.tSID = bs.sID
	inner join bbsUsers bu on bt.tUID = bu.UID
		group by sID ,UID,uName,sName